"""
指数数据访问层 (DAO)
提供指数相关数据的专用访问接口
"""

from typing import List, Optional, Dict, Any, Tuple
from datetime import datetime, date, timedelta
from sqlalchemy import select, and_, or_, desc, asc, func
from sqlalchemy.ext.asyncio import AsyncSession

from .base import FinancialDataDAO
from ..models.index import (
    IndexBasicInfo,
    IndexDailyData,
    IndexMinuteData,
    IndexConstituent,
    IndexValuation,
    IndexPerformance
)


class IndexBasicInfoDAO(FinancialDataDAO[IndexBasicInfo]):
    """指数基本信息数据访问层"""
    
    model = IndexBasicInfo
    
    async def get_by_index_code(self, session: AsyncSession, index_code: str) -> Optional[IndexBasicInfo]:
        """根据指数代码获取指数基本信息"""
        stmt = select(self.model).where(self.model.index_code == index_code)
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_by_exchange(self, session: AsyncSession, exchange: str) -> List[IndexBasicInfo]:
        """根据交易所获取指数列表"""
        stmt = select(self.model).where(self.model.exchange == exchange)
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_category(self, session: AsyncSession, category: str) -> List[IndexBasicInfo]:
        """根据分类获取指数列表"""
        stmt = select(self.model).where(self.model.category == category)
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_index_type(self, session: AsyncSession, index_type: str) -> List[IndexBasicInfo]:
        """根据指数类型获取指数列表"""
        stmt = select(self.model).where(self.model.index_type == index_type)
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_major_indices(self, session: AsyncSession) -> List[IndexBasicInfo]:
        """获取主要指数"""
        major_codes = [
            '000001.SH',  # 上证指数
            '399001.SZ',  # 深证成指
            '399006.SZ',  # 创业板指
            '000300.SH',  # 沪深300
            '000905.SH',  # 中证500
            '000852.SH',  # 中证1000
        ]
        
        stmt = select(self.model).where(self.model.index_code.in_(major_codes))
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_active_indices(self, session: AsyncSession) -> List[IndexBasicInfo]:
        """获取活跃指数"""
        stmt = select(self.model).where(self.model.status == 'active')
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def search_indices(
        self,
        session: AsyncSession,
        keyword: str,
        exchange: Optional[str] = None,
        category: Optional[str] = None
    ) -> List[IndexBasicInfo]:
        """搜索指数"""
        conditions = [
            or_(
                self.model.index_name.ilike(f'%{keyword}%'),
                self.model.index_code.ilike(f'%{keyword}%'),
                self.model.description.ilike(f'%{keyword}%')
            )
        ]
        
        if exchange:
            conditions.append(self.model.exchange == exchange)
        if category:
            conditions.append(self.model.category == category)
        
        stmt = select(self.model).where(and_(*conditions))
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_index_statistics(self, session: AsyncSession) -> Dict[str, int]:
        """获取指数统计信息"""
        # 按交易所统计
        exchange_stats = await session.execute(
            select(self.model.exchange, func.count(self.model.id))
            .group_by(self.model.exchange)
        )
        
        # 按分类统计
        category_stats = await session.execute(
            select(self.model.category, func.count(self.model.id))
            .group_by(self.model.category)
        )
        
        # 按类型统计
        type_stats = await session.execute(
            select(self.model.index_type, func.count(self.model.id))
            .group_by(self.model.index_type)
        )
        
        return {
            'total_indices': await session.scalar(select(func.count(self.model.id))),
            'by_exchange': dict(exchange_stats.all()),
            'by_category': dict(category_stats.all()),
            'by_type': dict(type_stats.all())
        }


class IndexDailyDataDAO(FinancialDataDAO[IndexDailyData]):
    """指数日行情数据访问层"""
    
    model = IndexDailyData
    
    async def get_by_index_and_date(
        self,
        session: AsyncSession,
        index_code: str,
        trade_date: date
    ) -> Optional[IndexDailyData]:
        """获取指定指数指定日期的行情数据"""
        stmt = select(self.model).where(
            and_(
                self.model.index_code == index_code,
                self.model.trade_date == trade_date
            )
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_price_history(
        self,
        session: AsyncSession,
        index_code: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None,
        limit: int = 1000
    ) -> List[IndexDailyData]:
        """获取指数价格历史数据"""
        conditions = [self.model.index_code == index_code]
        
        if start_date:
            conditions.append(self.model.trade_date >= start_date)
        if end_date:
            conditions.append(self.model.trade_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.trade_date))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_recent_data(
        self,
        session: AsyncSession,
        index_code: str,
        days: int = 30
    ) -> List[IndexDailyData]:
        """获取最近N天的指数行情数据"""
        from_date = datetime.now().date() - timedelta(days=days)
        return await self.get_price_history(session, index_code, from_date)
    
    async def get_latest_data(self, session: AsyncSession, index_code: str) -> Optional[IndexDailyData]:
        """获取指数最新行情数据"""
        stmt = (
            select(self.model)
            .where(self.model.index_code == index_code)
            .order_by(desc(self.model.trade_date))
            .limit(1)
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_market_overview(
        self,
        session: AsyncSession,
        trade_date: date,
        major_indices: Optional[List[str]] = None
    ) -> List[IndexDailyData]:
        """获取市场概览"""
        if major_indices is None:
            major_indices = [
                '000001.SH',  # 上证指数
                '399001.SZ',  # 深证成指
                '399006.SZ',  # 创业板指
                '000300.SH',  # 沪深300
                '000905.SH',  # 中证500
            ]
        
        stmt = select(self.model).where(
            and_(
                self.model.index_code.in_(major_indices),
                self.model.trade_date == trade_date
            )
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def calculate_index_returns(
        self,
        session: AsyncSession,
        index_code: str,
        periods: List[int] = [1, 5, 20, 60, 250]
    ) -> Dict[str, float]:
        """计算指数收益率"""
        latest_data = await self.get_recent_data(session, index_code, max(periods) + 5)
        
        if not latest_data:
            return {f'{period}d_return': 0.0 for period in periods}
        
        returns = {}
        current_price = float(latest_data[0].close_price)
        
        for period in periods:
            if len(latest_data) > period:
                past_price = float(latest_data[period].close_price)
                return_rate = (current_price - past_price) / past_price * 100
                returns[f'{period}d_return'] = return_rate
            else:
                returns[f'{period}d_return'] = 0.0
        
        return returns
    
    async def calculate_volatility(
        self,
        session: AsyncSession,
        index_code: str,
        days: int = 20
    ) -> float:
        """计算指数波动率"""
        price_data = await self.get_recent_data(session, index_code, days + 1)
        
        if len(price_data) < 2:
            return 0.0
        
        # 计算日收益率
        returns = []
        for i in range(len(price_data) - 1):
            current_price = float(price_data[i].close_price)
            previous_price = float(price_data[i + 1].close_price)
            daily_return = (current_price - previous_price) / previous_price
            returns.append(daily_return)
        
        if not returns:
            return 0.0
        
        # 计算标准差
        import statistics
        volatility = statistics.stdev(returns) if len(returns) > 1 else 0
        return volatility * (252 ** 0.5)  # 年化波动率
    
    async def get_correlation_matrix(
        self,
        session: AsyncSession,
        index_codes: List[str],
        days: int = 60
    ) -> Dict[str, Dict[str, float]]:
        """计算指数相关性矩阵"""
        # 获取所有指数的价格数据
        all_data = {}
        for index_code in index_codes:
            data = await self.get_recent_data(session, index_code, days + 1)
            if data:
                # 计算收益率序列
                returns = []
                for i in range(len(data) - 1):
                    current_price = float(data[i].close_price)
                    previous_price = float(data[i + 1].close_price)
                    daily_return = (current_price - previous_price) / previous_price
                    returns.append(daily_return)
                all_data[index_code] = returns
        
        # 计算相关性矩阵
        correlation_matrix = {}
        for code1 in index_codes:
            correlation_matrix[code1] = {}
            for code2 in index_codes:
                if code1 == code2:
                    correlation_matrix[code1][code2] = 1.0
                elif code1 in all_data and code2 in all_data:
                    returns1 = all_data[code1]
                    returns2 = all_data[code2]
                    
                    # 确保数据长度一致
                    min_length = min(len(returns1), len(returns2))
                    if min_length > 1:
                        import statistics
                        correlation = statistics.correlation(
                            returns1[:min_length], 
                            returns2[:min_length]
                        )
                        correlation_matrix[code1][code2] = correlation
                    else:
                        correlation_matrix[code1][code2] = 0.0
                else:
                    correlation_matrix[code1][code2] = 0.0
        
        return correlation_matrix
    
    async def get_technical_indicators(
        self,
        session: AsyncSession,
        index_code: str,
        days: int = 20
    ) -> Dict[str, float]:
        """计算技术指标"""
        price_data = await self.get_recent_data(session, index_code, days + 20)
        
        if len(price_data) < days:
            return {
                'ma5': 0.0,
                'ma10': 0.0,
                'ma20': 0.0,
                'rsi': 0.0,
                'bollinger_upper': 0.0,
                'bollinger_lower': 0.0
            }
        
        prices = [float(data.close_price) for data in reversed(price_data)]
        
        # 移动平均线
        ma5 = sum(prices[-5:]) / 5 if len(prices) >= 5 else 0
        ma10 = sum(prices[-10:]) / 10 if len(prices) >= 10 else 0
        ma20 = sum(prices[-20:]) / 20 if len(prices) >= 20 else 0
        
        # RSI
        rsi = 0.0
        if len(prices) >= 15:
            gains = []
            losses = []
            for i in range(1, 15):
                change = prices[-i] - prices[-i-1]
                if change > 0:
                    gains.append(change)
                    losses.append(0)
                else:
                    gains.append(0)
                    losses.append(abs(change))
            
            avg_gain = sum(gains) / 14
            avg_loss = sum(losses) / 14
            
            if avg_loss != 0:
                rs = avg_gain / avg_loss
                rsi = 100 - (100 / (1 + rs))
        
        # 布林带
        bollinger_upper = 0.0
        bollinger_lower = 0.0
        if len(prices) >= 20:
            import statistics
            std_dev = statistics.stdev(prices[-20:])
            bollinger_upper = ma20 + (2 * std_dev)
            bollinger_lower = ma20 - (2 * std_dev)
        
        return {
            'ma5': ma5,
            'ma10': ma10,
            'ma20': ma20,
            'rsi': rsi,
            'bollinger_upper': bollinger_upper,
            'bollinger_lower': bollinger_lower
        }


class IndexConstituentDAO(FinancialDataDAO[IndexConstituent]):
    """指数成分股数据访问层"""
    
    model = IndexConstituent
    
    async def get_by_index_code(
        self,
        session: AsyncSession,
        index_code: str,
        effective_date: Optional[date] = None
    ) -> List[IndexConstituent]:
        """获取指数成分股"""
        conditions = [self.model.index_code == index_code]
        
        if effective_date:
            conditions.append(self.model.effective_date <= effective_date)
            conditions.append(
                or_(
                    self.model.expiry_date.is_(None),
                    self.model.expiry_date > effective_date
                )
            )
        
        stmt = select(self.model).where(and_(*conditions))
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_stock_code(
        self,
        session: AsyncSession,
        stock_code: str,
        effective_date: Optional[date] = None
    ) -> List[IndexConstituent]:
        """获取股票所属的指数"""
        conditions = [self.model.stock_code == stock_code]
        
        if effective_date:
            conditions.append(self.model.effective_date <= effective_date)
            conditions.append(
                or_(
                    self.model.expiry_date.is_(None),
                    self.model.expiry_date > effective_date
                )
            )
        
        stmt = select(self.model).where(and_(*conditions))
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_constituent_changes(
        self,
        session: AsyncSession,
        index_code: str,
        start_date: date,
        end_date: date
    ) -> Dict[str, List[IndexConstituent]]:
        """获取成分股变化"""
        stmt = select(self.model).where(
            and_(
                self.model.index_code == index_code,
                self.model.effective_date >= start_date,
                self.model.effective_date <= end_date
            )
        ).order_by(desc(self.model.effective_date))
        
        result = await session.execute(stmt)
        changes = list(result.scalars().all())
        
        # 分类为新增和删除
        additions = [c for c in changes if c.change_type == 'add']
        deletions = [c for c in changes if c.change_type == 'delete']
        
        return {
            'additions': additions,
            'deletions': deletions
        }
    
    async def get_index_overlap(
        self,
        session: AsyncSession,
        index_code1: str,
        index_code2: str,
        effective_date: Optional[date] = None
    ) -> Dict[str, Any]:
        """计算两个指数的重叠度"""
        constituents1 = await self.get_by_index_code(session, index_code1, effective_date)
        constituents2 = await self.get_by_index_code(session, index_code2, effective_date)
        
        stocks1 = set(c.stock_code for c in constituents1)
        stocks2 = set(c.stock_code for c in constituents2)
        
        overlap = stocks1.intersection(stocks2)
        union = stocks1.union(stocks2)
        
        overlap_ratio = len(overlap) / len(union) if union else 0
        
        return {
            'overlap_count': len(overlap),
            'overlap_stocks': list(overlap),
            'overlap_ratio': overlap_ratio,
            'index1_unique': list(stocks1 - stocks2),
            'index2_unique': list(stocks2 - stocks1)
        }


class IndexMinuteDataDAO(FinancialDataDAO[IndexMinuteData]):
    """指数分钟数据访问层"""
    
    model = IndexMinuteData
    
    async def get_by_index_and_datetime(
        self,
        session: AsyncSession,
        index_code: str,
        trade_datetime: datetime
    ) -> Optional[IndexMinuteData]:
        """获取指定指数指定时间的分钟数据"""
        stmt = select(self.model).where(
            and_(
                self.model.index_code == index_code,
                self.model.trade_datetime == trade_datetime
            )
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_intraday_data(
        self,
        session: AsyncSession,
        index_code: str,
        trade_date: date
    ) -> List[IndexMinuteData]:
        """获取指数当日分钟数据"""
        start_datetime = datetime.combine(trade_date, datetime.min.time())
        end_datetime = datetime.combine(trade_date, datetime.max.time())
        
        stmt = select(self.model).where(
            and_(
                self.model.index_code == index_code,
                self.model.trade_datetime >= start_datetime,
                self.model.trade_datetime <= end_datetime
            )
        ).order_by(asc(self.model.trade_datetime))
        
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_recent_minutes(
        self,
        session: AsyncSession,
        index_code: str,
        minutes: int = 60
    ) -> List[IndexMinuteData]:
        """获取最近N分钟的数据"""
        stmt = (
            select(self.model)
            .where(self.model.index_code == index_code)
            .order_by(desc(self.model.trade_datetime))
            .limit(minutes)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())


class IndexValuationDAO(FinancialDataDAO[IndexValuation]):
    """指数估值数据访问层"""
    
    model = IndexValuation
    
    async def get_by_index_and_date(
        self,
        session: AsyncSession,
        index_code: str,
        valuation_date: date
    ) -> Optional[IndexValuation]:
        """获取指定指数指定日期的估值数据"""
        stmt = select(self.model).where(
            and_(
                self.model.index_code == index_code,
                self.model.valuation_date == valuation_date
            )
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_valuation_history(
        self,
        session: AsyncSession,
        index_code: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None,
        limit: int = 1000
    ) -> List[IndexValuation]:
        """获取指数估值历史数据"""
        conditions = [self.model.index_code == index_code]
        
        if start_date:
            conditions.append(self.model.valuation_date >= start_date)
        if end_date:
            conditions.append(self.model.valuation_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.valuation_date))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_latest_valuation(
        self,
        session: AsyncSession,
        index_code: str
    ) -> Optional[IndexValuation]:
        """获取指数最新估值数据"""
        stmt = (
            select(self.model)
            .where(self.model.index_code == index_code)
            .order_by(desc(self.model.valuation_date))
            .limit(1)
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_valuation_percentile(
        self,
        session: AsyncSession,
        index_code: str,
        metric: str = 'pe_ratio',
        days: int = 1000
    ) -> Dict[str, float]:
        """计算估值分位数"""
        history = await self.get_valuation_history(session, index_code, limit=days)
        
        if not history:
            return {'percentile': 0.0, 'current_value': 0.0}
        
        # 获取当前值
        current_valuation = history[0]
        current_value = getattr(current_valuation, metric, 0.0)
        
        # 计算历史分位数
        historical_values = [getattr(h, metric, 0.0) for h in history if getattr(h, metric, 0.0) > 0]
        
        if not historical_values:
            return {'percentile': 0.0, 'current_value': current_value}
        
        # 计算分位数
        sorted_values = sorted(historical_values)
        position = sum(1 for v in sorted_values if v <= current_value)
        percentile = position / len(sorted_values) * 100
        
        return {
            'percentile': percentile,
            'current_value': current_value,
            'min_value': min(historical_values),
            'max_value': max(historical_values),
            'median_value': sorted_values[len(sorted_values) // 2]
        }


class IndexPerformanceDAO(FinancialDataDAO[IndexPerformance]):
    """指数表现数据访问层"""
    
    model = IndexPerformance
    
    async def get_by_index_and_period(
        self,
        session: AsyncSession,
        index_code: str,
        period_type: str,
        period_value: str
    ) -> Optional[IndexPerformance]:
        """获取指定指数指定周期的表现数据"""
        stmt = select(self.model).where(
            and_(
                self.model.index_code == index_code,
                self.model.period_type == period_type,
                self.model.period_value == period_value
            )
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_performance_summary(
        self,
        session: AsyncSession,
        index_code: str
    ) -> List[IndexPerformance]:
        """获取指数表现汇总"""
        stmt = select(self.model).where(
            self.model.index_code == index_code
        ).order_by(asc(self.model.period_type), asc(self.model.period_value))
        
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_performance_ranking(
        self,
        session: AsyncSession,
        period_type: str,
        period_value: str,
        metric: str = 'total_return'
    ) -> List[IndexPerformance]:
        """获取指数表现排名"""
        order_field = getattr(self.model, metric, self.model.total_return)
        
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.period_type == period_type,
                    self.model.period_value == period_value
                )
            )
            .order_by(desc(order_field))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def compare_performance(
        self,
        session: AsyncSession,
        index_codes: List[str],
        period_type: str,
        period_value: str
    ) -> List[IndexPerformance]:
        """比较多个指数的表现"""
        stmt = select(self.model).where(
            and_(
                self.model.index_code.in_(index_codes),
                self.model.period_type == period_type,
                self.model.period_value == period_value
            )
        ).order_by(desc(self.model.total_return))
        
        result = await session.execute(stmt)
        return list(result.scalars().all())